package com.huangpuweb.api.service.imp;

import com.huangpuweb.api.model.HpPersonCnts;
import com.huangpuweb.api.model.HpPersonCntsRowMapper;
import com.huangpuweb.api.model.HpSynthesisJudicature;
import com.huangpuweb.api.model.HpSynthesisJudicatureRowMapper;
import com.huangpuweb.api.service.SynthesisService;
import com.huangpuweb.api.util.StringUtil;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Service
public class SynthesisServiceImpl extends BaseServiceSynthesisDataSource57 implements SynthesisService {
    @Override
    public List<HpPersonCnts> getChangeTrendInfoByStatus(String year, String status) {
        String sql="SELECT adress,sum(number_cnts) sum from app_person_cnts WHERE SUBSTR(times from 1 for 4)=? and status=? group by adress";
        return getEntryList(sql, new RowMapper<HpPersonCnts>() {
            @Override
            public HpPersonCnts mapRow(ResultSet rs, int rowNum) throws SQLException {
                HpPersonCnts hp=new HpPersonCnts();
                hp.setAdress(rs.getString("adress"));
                hp.setSum(rs.getString("sum"));
                return hp;
            }
        }, new Object[]{year, status});
    }

    @Override
    public List<HpPersonCnts> getHpPersonCntsListByStatus(String status) {
        String sql="SELECT s.status, number_cnts ,times,adress FROM app_person_cnts s WHERE times =( SELECT MAX( times ) FROM app_person_cnts WHERE status = ? ) AND status=? ";
        return getEntryList(sql, new RowMapper<HpPersonCnts>() {
            @Override
            public HpPersonCnts mapRow(ResultSet rs, int rowNum) throws SQLException {
                HpPersonCnts hp=new HpPersonCnts();
                hp.setAdress(rs.getString("adress"));
                hp.setSum(rs.getString("number_cnts"));
                return hp;
            }
        }, new Object[]{status,status});
    }

    @Override
    public List<HpPersonCnts> getChangeTrendMonthByStatus(String string, String year) {
        String sql="select status,times,sum(number_cnts) sum from app_person_cnts WHERE status=? and substr(times from 1 for 4 )=?" +
                "group by status,times";
        return getEntryList(sql, new RowMapper<HpPersonCnts>() {
            @Override
            public HpPersonCnts mapRow(ResultSet rs, int rowNum) throws SQLException {
                HpPersonCnts hp=new HpPersonCnts();
                hp.setStatus(rs.getString("status"));
                hp.setSum(rs.getString("sum"));
                hp.setTimes(rs.getString("times"));
                return hp;
            }
        },new Object[]{string,year});
    }

    /**
     * 获取特殊人口列表
     * @param hpPersonCnts
     * @return
     */
    public List<HpPersonCnts> getSpecialPersonList(HpPersonCnts hpPersonCnts){
        String sql = "select id, times, adress, number_cnts, status from app_person_cnts where 1=1";
        List<Object> paramsList = new ArrayList<>();
        if(!StringUtil.isEmpty(hpPersonCnts.getStatus())){
            sql = sql + " and status = ? ";
            paramsList.add(hpPersonCnts.getStatus());
        }
        if(!StringUtil.isEmpty(hpPersonCnts.getYear())){
            sql = sql + " and times like '%"+ hpPersonCnts.getYear() +"%' ";
        }
        return getEntryList(sql, new RowMapper<HpPersonCnts>() {
            @Override
            public HpPersonCnts mapRow(ResultSet rs, int rowNum) throws SQLException {
                HpPersonCnts hp=new HpPersonCnts();
                hp.setId(rs.getInt("id"));
                hp.setTimes(rs.getString("times"));
                hp.setAdress(rs.getString("adress"));
                hp.setNumber_cnts(rs.getString("number_cnts"));
                hp.setStatus(rs.getString("status"));
                return hp;
            }
        },paramsList.toArray());
    }

    @Override
    public List<HpPersonCnts> getChangeTrendInfo( ) {
        String sql="select status,SUBSTR(times FROM 1 FOR 4) as time,sum(number_cnts) as sum from app_person_cnts group by status,SUBSTR(times FROM 1 FOR 4)";
        return getEntryList(sql, new RowMapper<HpPersonCnts>() {
            @Override
            public HpPersonCnts mapRow(ResultSet rs, int rowNum) throws SQLException {
                HpPersonCnts hp=new HpPersonCnts();
                hp.setTimes(rs.getString("time"));
                hp.setSum(rs.getString("sum"));
                hp.setStatus(rs.getString("status"));
                return hp;
            }
        },new Object[]{});
    }

    @Override
    public HpPersonCnts getHpPersonCntsCount(String status) {
//        String sql="SELECT s.status,sum(s.number_cnts) sum from (SELECT *  from app_person_cnts WHERE status=? ORDER BY times desc limit 0,10) s";
        String sql="SELECT s.status,SUM( number_cnts ) sum FROM app_person_cnts s WHERE times =( SELECT MAX( times ) FROM app_person_cnts WHERE status =? ) " +
                "AND status = ?";
        return getEntry(sql, new RowMapper<HpPersonCnts>() {
            @Override
            public HpPersonCnts mapRow(ResultSet rs, int rowNum) throws SQLException {
                HpPersonCnts hpPersonCnts=new HpPersonCnts();
                hpPersonCnts.setStatus(rs.getString("status"));
                hpPersonCnts.setSum(rs.getString("sum"));
                return hpPersonCnts;
            }
        },new Object[]{status,status});
    }

    @Override
    public List<HpSynthesisJudicature> getSpecialPersonnelCount() {
        String sql="select status,sum(number) sum from app_judicature GROUP BY status ";
        return getEntryList(sql, new RowMapper<HpSynthesisJudicature>() {
            @Override
            public HpSynthesisJudicature mapRow(ResultSet rs, int rowNum) throws SQLException {
                HpSynthesisJudicature hp=new HpSynthesisJudicature();
                hp.setStatus(rs.getString("status"));
                hp.setSum(rs.getString("sum"));
                return hp;
            }
        },new Object[]{});
    }

    @Override
    public List<HpSynthesisJudicature> getSpecialPersonnelList(String status) {
        String sql="select * from app_judicature where status=?";
        return getEntryList(sql,new HpSynthesisJudicatureRowMapper(),new Object[]{status});
    }
}
